package com.kuhh.dao.impl;

import com.kuhh.dao.IProfessionCourseDao;
import com.kuhh.pojo.ProfessionCourse;
import com.kuhh.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProfessionCourseDaoImpl implements IProfessionCourseDao {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @Override
    public List<ProfessionCourse> getProfessionCourseList() {
        List<ProfessionCourse> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from tb_profession_course order by create_time desc";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                list.add(professionCourse);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<ProfessionCourse> getCourseByProfessionNo(String professionNo) {
        List<ProfessionCourse> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from tb_profession_course where profession_no = ? and course_type = '1' order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,professionNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                list.add(professionCourse);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public ProfessionCourse getProfessionCourseByNo(String courseNo) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from tb_profession_course where course_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                return professionCourse;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return null;
    }

    @Override
    public boolean updateProfessionCourse(ProfessionCourse professionCourse) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "update tb_profession_course set course_name = ?,course_type = ?,course_credit = ?,course_num = ?," +
                    "grade_no = ?,profession_no = ?,teacher_no = ?,update_time = ? where course_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,professionCourse.getCourseName());
            ps.setString(2,professionCourse.getCourseType());
            ps.setString(3,professionCourse.getCourseCredit());
            ps.setInt(4,professionCourse.getCourseNum());
            ps.setString(5,professionCourse.getGradeNo());
            ps.setString(6,professionCourse.getProfessionNo());
            ps.setString(7,professionCourse.getTeacherNo());
            ps.setDate(8, new Date(professionCourse.getUpdateTime().getTime()));
            ps.setString(9,professionCourse.getCourseNo());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public boolean addProfessionCourse(ProfessionCourse professionCourse) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into tb_profession_course values(?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,professionCourse.getCourseNo());
            ps.setString(2,professionCourse.getCourseName());
            ps.setString(3,professionCourse.getCourseType());
            ps.setString(4,professionCourse.getCourseCredit());
            ps.setInt(5,professionCourse.getCourseNum());
            ps.setString(6,professionCourse.getGradeNo());
            ps.setString(7,professionCourse.getProfessionNo());
            ps.setString(8,professionCourse.getTeacherNo());
            ps.setDate(9, new Date(professionCourse.getCreateTime().getTime()));
            ps.setDate(10, new Date(professionCourse.getUpdateTime().getTime()));
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public boolean deleteProfessionCourseByNo(String courseNo) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "delete from tb_profession_course where course_no = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public List<ProfessionCourse> getCourseByTeacherNo(String studentTeacherNo) {
        List<ProfessionCourse> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from tb_profession_course where teacher_no = ? order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentTeacherNo);
            rs = ps.executeQuery();
            while (rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                list.add(professionCourse);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }
}
